#!/usr/bin/env node
/* 强制重建 pph_records 表（不备份、直接重建） */
const path = require('path');
const sqlite3 = require('sqlite3').verbose();

const dbPath = path.join(__dirname, '..', 'data', 'pph.sqlite');
const db = new sqlite3.Database(dbPath);

const PPH_RECORDS_CREATE_SQL = `
CREATE TABLE IF NOT EXISTS pph_records (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  record_date DATE NOT NULL,
  line_name VARCHAR(50) NOT NULL,
  order_number VARCHAR(100) NOT NULL,
  material_code VARCHAR(50) NOT NULL,
  product_name VARCHAR(200) NOT NULL,
  product_series VARCHAR(100),
  start_time TIME NOT NULL,
  end_time TIME NOT NULL,
  -- 订单相关（可为空）
  order_quantity INTEGER,
  inbound_quantity INTEGER,
  completed_total_quantity INTEGER,
  to_complete_quantity INTEGER,
  target_record_date DATE,
  -- 数量与人员
  completed_quantity INTEGER,
  quantity INTEGER NOT NULL DEFAULT 0,
  online_staff INTEGER DEFAULT 0,
  offline_staff INTEGER DEFAULT 0,
  total_people INTEGER NOT NULL DEFAULT 1,
  working_people INTEGER,
  total_staff INTEGER,
  -- 质量与损失
  defect_quantity INTEGER DEFAULT 0,
  rework_quantity INTEGER DEFAULT 0,
  scrap_quantity INTEGER DEFAULT 0,
  abnormal_loss_minutes DECIMAL(8,2) DEFAULT 0,
  changeover_minutes DECIMAL(8,2) DEFAULT 0,
  break_minutes DECIMAL(8,2) DEFAULT 0,
  maintenance_time DECIMAL(8,2) DEFAULT 0,
  belt_speed DECIMAL(8,2),
  -- 计算字段（可选直存）
  calculated_pph DECIMAL(8,2),
  productivity_index DECIMAL(8,2),
  time_utilization_rate DECIMAL(8,2),
  quality_rate DECIMAL(8,2),
  defect_rate DECIMAL(8,2),
  total_person_hours DECIMAL(8,2),
  effective_person_hours DECIMAL(8,2),
  person_utilization_rate DECIMAL(8,2),
  actual_takt_seconds DECIMAL(8,2),
  -- 生产类型标记（1=整机生产，0=整机+电机生产）
  is_whole_machine INTEGER,
  -- 异常信息
  abnormality_category VARCHAR(100),
  abnormality_description TEXT,
  downtime_reason VARCHAR(200),
  -- 系统
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  created_by VARCHAR(50) DEFAULT 'system',
  sync_status VARCHAR(20) DEFAULT 'synced'
);
`;

function exec(sql) {
  return new Promise((resolve, reject) => db.run(sql, (err) => err ? reject(err) : resolve()));
}

(async () => {
  try {
    await exec('BEGIN IMMEDIATE TRANSACTION');
    await exec('DROP TABLE IF EXISTS pph_records');
    await exec(PPH_RECORDS_CREATE_SQL);
    // 唯一约束：A1口径（record_date,line_name,material_code,start_time,end_time）
    await exec("CREATE UNIQUE INDEX IF NOT EXISTS uq_pph_records_a1 ON pph_records(record_date, line_name, material_code, start_time, end_time)");
    await exec('COMMIT');
    console.log(JSON.stringify({ success: true, rebuilt: true }));
  } catch (e) {
    try { await exec('ROLLBACK'); } catch {}
    console.error(JSON.stringify({ success: false, error: e.message }));
    process.exit(1);
  } finally {
    db.close();
  }
})();
